﻿using BTS.Models;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Data.SQLite;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;


namespace BTS.Helpers.DataBaseHelper
{
    public class AccessDataBase : IDataBase
    {

        private static string accdbTemplate = @"..\..\Data\AccessTemplate\Template.accdb";
        private static string mdbTemplate = @"..\..\Data\AccessTemplate\Template.mdb";


        private static bool Is64Bit()
        {
            string p = System.Diagnostics.Process.GetCurrentProcess().ProcessName;
            System.Diagnostics.Process[] processes = System.Diagnostics.Process.GetProcessesByName(p);

            if (processes.Length > 0)
            {
                Process process = processes[0];
                IntPtr handle = process.MainWindowHandle;
                uint processId = (uint)process.Id;
                return Is64BitProcess(processId);
            }
            return false;
        }
        private static bool Is64BitProcess(uint processId)
        {
            bool isWow64 = false;
            IntPtr handle = IntPtr.Zero;
            try
            {
                handle = Process.GetProcessById((int)processId).Handle;
                if (!NativeMethods.IsWow64Process(handle, out isWow64))
                {
                    throw new Exception("Failed to determine if process is 64-bit.");
                }
            }
            finally
            {
                if (handle != IntPtr.Zero)
                {
                    NativeMethods.CloseHandle(handle);
                }
            }
            return !isWow64;
        }
        internal static class NativeMethods
        {
            [System.Runtime.InteropServices.DllImport("kernel32.dll", SetLastError = true)]
            [return: System.Runtime.InteropServices.MarshalAs(System.Runtime.InteropServices.UnmanagedType.Bool)]
            public static extern bool IsWow64Process(IntPtr hProcess, out bool lpSystemInfo);

            [System.Runtime.InteropServices.DllImport("kernel32.dll", SetLastError = true)]
            public static extern bool CloseHandle(IntPtr hObject);
        }


        public AccessDataBase(string dbFilePath,string psw="")
        {
             _connectionString = SetConnectionString(dbFilePath, psw);
            
        }
        private  string SetConnectionString(string dbFilePath, string psw)
        {
            string connection = "";
            if (Is64Bit()) //根据软件的版本来确定数据库的版本
            {
                if (Path.GetExtension(dbFilePath) == ".mdb")
                {
                    dbFilePath = dbFilePath.Replace("mdb", "accdb");
              
                }
              else  if (Path.GetExtension(dbFilePath) == ".db")
                {
                    dbFilePath = dbFilePath.Replace("db", "accdb");
                }


                _dbFilePath = dbFilePath;
                //64位
                return connection = $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={dbFilePath};User Id=; Jet OLEDB:Database Password={psw};";
            }
            else
            {
                if (Path.GetExtension(dbFilePath) == ".accdb")
                {
                    dbFilePath = dbFilePath.Replace("accdb", "mdb");
                }
              else  if (Path.GetExtension(dbFilePath) == ".db")
                {
                    dbFilePath = dbFilePath.Replace("db", "mdb");
                }

                _dbFilePath = dbFilePath;
                //X86位
                return  connection = $"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={dbFilePath};User Id=; Jet OLEDB:Database Password={psw};";
            }
        }

        private readonly string _connectionString;
        private  string _dbFilePath;

        public bool AddColumnName(string Table, DBColumnModel[] Cols)
        {
            try
            {
                string sql = "";
                bool blErrFlag = true;
                for (int i = 0; i < Cols.Length; i++)
                {
                    sql = $"ALTER TABLE {Table} ADD {Cols[i].ColName} {Cols[i].Description}";

                    using (var connection = new OleDbConnection(_connectionString))
                    {
                        connection.Open();
                        using (var command = new OleDbCommand(sql, connection))
                        {
                            if (command.ExecuteNonQuery() >= 0)
                                continue;
                            else
                            {
                                blErrFlag = false;
                                break;
                            }
                        }
                    }
                }
                return blErrFlag;

            }
            catch (Exception)
            {
                return false;
            }
        }

        public bool Clear(string Table)
        {
            try
            {
                string sql = $"DELETE FROM {Table}";
                using (var connection = new OleDbConnection(_connectionString))
                {
                    connection.Open();
                    using (var command = new OleDbCommand(sql, connection))
                    {
                        if (command.ExecuteNonQuery() >= 0)
                            return true;
                        else
                            return false;
                    }
                }
            }
            catch (Exception)
            {
                return false;
            }
        }

        public bool Compress()
        {
            return true;
            //try
            //{
            //    object[] oParams;
            //    string mdwfilename = _dbFilePath;
            //    object objJRO = Activator.CreateInstance(Type.GetTypeFromProgID("JRO.JetEngine"));
            //    oParams = new object[] { _connectionString, "Provider=Microsoft.Jet.OLEDB.4.0;Data source=C:\\temp.mdb;Jet OLEDB:Engine Type=5;Jet OLEDB:Database Password=sbgc201510;" };
            //    objJRO.GetType().InvokeMember("CompactDatabase", System.Reflection.BindingFlags.InvokeMethod, null, objJRO, oParams);
            //    System.IO.File.Delete(mdwfilename);
            //    System.IO.File.Move("C:\\temp.mdb", mdwfilename);
            //    System.Runtime.InteropServices.Marshal.ReleaseComObject(objJRO);
            //    objJRO = null;
            //    System.IO.File.Delete("C:\\temp.mdb");
            //    return true;
            //}
            //catch (Exception)
            //{
            //    return false;
            //}
         

        }
        
        /// <summary>
        /// 复制且重命名数据库到工作目录下
        /// </summary>
        private static bool CopyAccessDB(string fileNameTemplate)
        {
            try
            {
                //判断路径是否存在，若不存在就创建
                if (!Directory.Exists(Path.GetDirectoryName(fileNameTemplate)))
                {
                    Directory.CreateDirectory(Path.GetDirectoryName(fileNameTemplate));
                }

                string fileName = Path.GetFileName(fileNameTemplate);
                //判断文件是否存在
                if (File.Exists(fileNameTemplate))
                {
                    if (!File.Exists(fileName))
                    {
                        File.Copy(fileNameTemplate, fileName);
                        return true;
                    }
                    else return true;

                }
                else return false;
            }
            catch
            {
                return false;
            }
        }
        public bool CreateNewDBFile(string dbFilePath,string PSW)
        {
            try
            {
                if (File.Exists(_dbFilePath))//数据库文件已存在
                {
                    return true;
                }

                //检查并创建目录文件
                if (!Directory.Exists(Path.GetDirectoryName(dbFilePath)))
                {
                    Directory.CreateDirectory(Path.GetDirectoryName(dbFilePath));
                }

                string connectionString = SetConnectionString(dbFilePath, PSW);

                //创建库文件
                CopyAccessDB(connectionString);

                //ADOX.Catalog catalog = new ADOX.Catalog();
                //catalog.Create(connectionString);

                //检查数据库连接是否正常
                using (OleDbConnection connection = new OleDbConnection(connectionString))
                {
                    connection.Open();
                }
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        public bool CreateTable(string Table, string CreateCmd)
        {
            try
            {
                string sql = $"CREATE TABLE {Table} ({CreateCmd});";
                using (var connection = new OleDbConnection(_connectionString))
                {
                    connection.Open();
                    using (var command = new OleDbCommand(sql, connection))
                    {
                        if (command.ExecuteNonQuery() >= 0)
                            return true;
                        else
                            return false;
                    }
                }
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        public bool Delete(string Table, string query)
        {
            try
            {
                string sql = $"DELETE FROM {Table} WHERE {query}";
                using (var connection = new OleDbConnection(_connectionString))
                {
                    connection.Open();
                    using (var command = new OleDbCommand(sql, connection))
                    {
                        if (command.ExecuteNonQuery() >= 0)
                            return true;
                        else
                            return false;
                    }
                }
            }
            catch (Exception)
            {
                return false;
            }
        }

        public string[] GetAllColumnsName(string Table)
        {
            string[] colNames = null;
            try
            {

                DataTable dataTable = new DataTable();

                string sql = $"SELECT TOP 1 * FROM {Table}";
                using (var connection = new OleDbConnection(_connectionString))
                {
                    connection.Open();
                    using (var command = new OleDbCommand(sql, connection))
                    {
                        using (OleDbDataReader reader = command.ExecuteReader())
                        {
                            dataTable.Load(reader);
                        }

                        colNames = new string[dataTable.Columns.Count];
                        for (int i = 0; i < dataTable.Columns.Count; i++)
                        {
                            colNames[i] = dataTable.Columns[i].ColumnName;
                        }

                        return colNames;
                    }
                }
            }
            catch (Exception)
            {
                return colNames;
            }
        }

        public int GetTotalRows(string query)
        {
            int totalCount = 0;
            try
            {
                using (var connection = new OleDbConnection(_connectionString))
                {
                    connection.Open();
                    using (var command = new OleDbCommand(query, connection))
                    {
                        totalCount = Convert.ToInt32(command.ExecuteScalar());
                        return totalCount;
                    }
                }
            }
            catch (Exception)
            {
                return totalCount;
            }
        }

        public bool Insert(string Table, string query)
        {
            try
            {
                string sql = $"INSERT INTO {Table}{query}";
                using (var connection = new OleDbConnection(_connectionString))
                {
                    connection.Open();
                    using (var command = new OleDbCommand(sql, connection))
                    {
                        if (command.ExecuteNonQuery() >= 0)
                            return true;
                        else
                            return false;
                    }
                }
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        public DataTable QueryAll(string Table)
        {
            DataTable dt = new DataTable();
            try
            {

                string sql = $"SELECT *FROM {Table}";
                using (var connection = new OleDbConnection(_connectionString))
                {
                    connection.Open();
                    using (var command = new OleDbCommand(sql, connection))
                    {
                        command.ExecuteNonQuery();
                        using (OleDbDataAdapter da = new OleDbDataAdapter(command))
                        {
                            da.Fill(dt); // 使用DataAdapter填充DataTable
                        }
                        return dt;
                    }
                }
            }
            catch (Exception)
            {
                return dt;
            }
        }

        public DataTable QuerySomething(string query)
        {
            DataTable dt = new DataTable();
            try
            {
                string sql = $"SELECT {query}";
                using (var connection = new OleDbConnection(_connectionString))
                {
                    connection.Open();
                    using (var command = new OleDbCommand(sql, connection))
                    {
                        command.ExecuteNonQuery();
                        using (OleDbDataAdapter da = new OleDbDataAdapter(command))
                        {
                            da.Fill(dt); // 使用DataAdapter填充DataTable
                        }
                        return dt;
                    }
                }
            }
            catch (Exception)
            {
                return dt;
            }
        }

        public bool Update(string Table, string query)
        {
            try
            {

                string sql = $"UPDATE {Table} SET {query}";

                using (var connection = new OleDbConnection(_connectionString))
                {
                    connection.Open();
                    using (var command = new OleDbCommand(sql, connection))
                    {
                        if (command.ExecuteNonQuery() >= 0)
                            return true;
                        else
                            return false;
                    }
                }
            }
            catch (Exception)
            {
                return false;
            }
        }
    }
}
